﻿namespace Manhua2.Common
{
    using Manhua2.Helper;
    using Manhua2.SQLiteTable;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;

    /// <summary>
    /// 封装与数据库交互的所有逻辑
    /// </summary>
    public sealed class DbContext : IDisposable
    {
        #region SharedConnection
        private class SharedConnection
        {
            private static object _lockPoint = new object();
            private static SQLite.SQLiteAsyncConnection connection;
            public static SQLite.SQLiteAsyncConnection GetConnection()
            {
                if (connection == null)
                    connection = new SQLite.SQLiteAsyncConnection(Global.DataBase, true);

                if (connection == null)
                    throw new Exception("无法加载数据库, 请重新启动程序...");

                return connection;
            }

            public async static Task Open()
            {
                using (LockWrapper _lock = new LockWrapper(_lockPoint))
                {
                    if (!await MethodHelper.FileIsExists(Global.DataBase))
                    {
                        var source = await MethodHelper.LoadFileFromInstall("Assets\\" + Global.DataBase);
                        var dest = await source.CopyAsync(Windows.Storage.ApplicationData.Current.LocalFolder);
                    }

                    connection = new SQLite.SQLiteAsyncConnection(Global.DataBase, true);
                }
            }
        }
        #endregion

        public DbContext()
        {
        }

        public async static Task InitializeDb()
        {
            await SharedConnection.Open();
        }

        public void BeginTransaction()
        {
            var conn = SharedConnection.GetConnection();
            conn.BeginTransaction();
        }

        public void Commit()
        {
            var conn = SharedConnection.GetConnection();
            conn.Commit();
        }

        public void Rollback()
        {
            var conn = SharedConnection.GetConnection();
            conn.Rollback();
        }

        public async Task SaveHistory(int chapterId, int page)
        {
            var conn = SharedConnection.GetConnection();

            var his = await conn.FirstOrDefault<SQLiteHistory>(
                                    "SELECT * FROM histories WHERE chapter_id = ?"
                                    , chapterId);

            if (his == null)
            {
                his = new SQLiteHistory()
                {
                    ChapterId = chapterId,
                    Page = page,
                    LastView = DateTime.Now,
                };

                await this.InsertAsync(his);
            }
            else
            {
                his.LastView = DateTime.Now;
                his.Page = page;
                await this.UpdateAsync(his);
            }
        }

        public Task<int> ClearPages(int chapterId)
        {
            var conn = SharedConnection.GetConnection();
            return conn.ExecuteAsync(
                "DELETE FROM pages WHERE chapter_id = ?",
                chapterId);
        }

        public Task<int> InsertAsync<T>(T entity)
        {
            var conn = SharedConnection.GetConnection();
            return conn.InsertAsync(entity);
        }

        public Task<int> UpdateAsync<T>(T entity)
        {
            var conn = SharedConnection.GetConnection();
            return conn.UpdateAsync(entity);
        }

        public Task<List<T>> QueryAsync<T>(string sql, params object[] args) where T : new()
        {
            var conn = SharedConnection.GetConnection();
            return conn.QueryAsync<T>(sql, args);
        }

        public Task<T> GetAsync<T>(object key) where T : new()
        {
            var conn = SharedConnection.GetConnection();
            return conn.GetAsync<T>(key);
        }

        public Task<T> FirstOrDefault<T>(string sql, params object[] args) where T: new()
        {
            var conn = SharedConnection.GetConnection();
            return conn.FirstOrDefault<T>(sql, args);
        }

        public Task<int> ExecuteAsync(string sql, params object[] args)
        {
            var conn = SharedConnection.GetConnection();
            return conn.ExecuteAsync(sql, args);
        }

        #region 数据库逻辑操作
        /// <summary>
        /// 查询该章节所有页面的图片地址。
        /// </summary>
        /// <param name="chapterId"></param>
        /// <returns></returns>
        public async Task<List<string>> QueryPageUrlsAsync(int chapterId)
        {
            var result = await this.QueryAsync<SQLiteTable.SQLitePage>(
                                     "SELECT url FROM pages WHERE chapter_id = ?", chapterId);

            return result.Select(k => k.Url).ToList();
        }

        public async Task InsertChapter(string title, int comicId, DateTime dt, string url = "")
        {
            var chapter = await this.GetChapter(title, comicId);
            if (chapter == null)
            {
                chapter = new SQLiteChapter();
                chapter.ComicId = comicId;

                chapter.Title = title;
                chapter.Updated = dt;

                chapter.Url = url;

                await this.InsertAsync(chapter);
            }
            else
            {
                chapter.Url = url;
                await this.UpdateAsync(chapter);
            }
        }

        public async Task<SQLiteComic> GetComic(string title, string provider)
        {
            return await this.FirstOrDefault<SQLiteComic>(
                            "SELECT * FROM comics WHERE title = ? AND provider = ? LIMIT 1", title, provider);
        }

        public async Task<SQLiteChapter> GetChapter(string title, int comicId)
        {
            return await this.FirstOrDefault<SQLiteChapter>(
                            "SELECT * FROM chapters WHERE title = ? AND comic_id = ? LIMIT 1",
                            title, comicId);
        }

        public async Task<SQLiteChapter> GetChapterLatest(int comicId)
        {
            return await this.FirstOrDefault<SQLiteChapter>(
                            "SELECT * FROM chapters WHERE comic_id = ? ORDER BY updated DESC LIMIT 1",
                            comicId);
        }
        #endregion

        public void Dispose()
        {
        }
    }
}
